﻿Public Class CodeGeneratorForm
    Const CST_GETTABLES_SQL As String = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = '{0}' AND TABLE_NAME NOT IN ({1}) AND TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_TYPE ASC, TABLE_NAME ASC"
    Const CST_GETFIELDS_SQL As String = "SELECT DISTINCT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ({0}) ORDER BY TABLE_NAME, ORDINAL_POSITION"
    Const CST_GETTABLESCMDTEXT As String = "select Tab.object_id as 'TableID', Tab.Name as 'TableName', Col.column_id as 'ColumnID', Col.Name as 'ColumnName', Col.is_identity as 'IsIdentity', Ty.Name as 'ColumnTypeName', Col.max_length as 'MaxLength', Col.is_nullable as 'IsNullable', Col.is_rowguidcol as 'IsUnique' from sys.tables as Tab INNER JOIN sys.Columns as Col ON Tab.object_ID = Col.object_id INNER JOIN sys.Types as Ty ON Col.system_type_id = Ty.system_type_id WHERE	Tab.Name <> 'dtproperties' AND Tab.Name <> 'sysdiagrams' AND Ty.Name <> 'sysname' ORDER BY	TableName"

    Dim aSerializer As System.Xml.Serialization.XmlSerializer
    Private databaseTableList As New List(Of DataBaseTable)

    Dim xmlFilePath As String
    Dim resultFilePath As String
    Dim templateFilePath As String


    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()


        ' Add any initialization after the InitializeComponent() call.
        Me.Cursor = Cursors.WaitCursor

        InitData()
        LoadTreeViewTable()

        Me.Cursor = Cursors.Default
    End Sub

    Private Sub InitData()
        Using command As SqlClient.SqlCommand = CommonExt.Base.Connection.Command
            Dim myReader As SqlClient.SqlDataReader = Nothing

            command.CommandText = CST_GETTABLESCMDTEXT
            myReader = command.ExecuteReader()

            Dim aDataBaseTable As DataBaseTable = New DataBaseTable()
            Dim tableName As String = String.Empty

            While myReader.Read
                If tableName <> myReader.GetString(myReader.GetOrdinal("TableName")) Then
                    tableName = myReader.GetString(myReader.GetOrdinal("TableName"))
                    aDataBaseTable = New DataBaseTable(tableName)
                    databaseTableList.Add(aDataBaseTable)
                End If

                aDataBaseTable.TableColumnsList.Add(FillObject(myReader))
            End While



        End Using
    End Sub

    Private Function FillObject(myReader As SqlClient.SqlDataReader) As ActiveFramework.CommonExt.DataBaseTableColumn
        Dim tbCol As New ActiveFramework.CommonExt.DataBaseTableColumn()

        tbCol.ColumnID = myReader.GetInt32(myReader.GetOrdinal("ColumnID"))
        tbCol.ColumnName = myReader.GetString(myReader.GetOrdinal("ColumnName"))
        tbCol.ColumnTypeName = myReader.GetString(myReader.GetOrdinal("ColumnTypeName"))
        tbCol.MaxLength = CInt(myReader.GetValue(myReader.GetOrdinal("MaxLength")))
        tbCol.IsNullable = myReader.GetBoolean(myReader.GetOrdinal("IsNullable"))
        tbCol.IsUnique = myReader.GetBoolean(myReader.GetOrdinal("IsUnique"))

        'Do the mapping between SQL Data Type and VB Type
        tbCol.PropertyTypeName = GetPropertyTypeString(tbCol.ColumnTypeName, tbCol.IsNullable)

        Return tbCol
    End Function

    Public Function GetPropertyTypeString(propertyName As String, isNullable As Boolean) As String
        Dim propertyString As String = String.Empty

        Select Case propertyName
            Case "uniqueidentifier"
                propertyString = "Guid"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select

            Case "int", "smallint"
                propertyString = "Integer"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select

            Case "timestamp", "binary"
                propertyString = "Byte"
                Exit Select

            Case "bit"
                propertyString = "Boolean"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select

            Case "nvarchar", "varchar", "char", "nchar", "text"
                propertyString = "String"
                Exit Select

            Case "decimal", "numeric", "money", "smallmoney"
                propertyString = "Decimal"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select

            Case "real"
                propertyString = "Single"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select

            Case "flaot"
                propertyString = "Double"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select

            Case "datetime", "smalldatetime"
                propertyString = "Date"
                If isNullable Then
                    propertyString += "?"
                End If
                Exit Select
            Case Else

                Exit Select
        End Select


        

        Return propertyString
    End Function



    Private Sub LoadTreeViewTable()
        Dim tableID As Integer = 0
        Dim parentNode As TreeNode = Nothing


        If databaseTableList IsNot Nothing AndAlso databaseTableList.Count > 0 Then
            For Each aTable As DataBaseTable In databaseTableList
                parentNode = AddParentNode(aTable)
                For Each aCol As DataBaseTableColumn In aTable.TableColumnsList
                    AddChildNode(aCol, parentNode)
                Next
            Next
        End If
    End Sub

    Private Function AddParentNode(ByVal tabCol As DataBaseTable) As TreeNode
        Dim aNode As New TreeNode()
        aNode.Tag = tabCol
        aNode.Text = tabCol.TableName
        TreeViewTable.Nodes.Add(aNode)

        Return aNode
    End Function

    Private Sub AddChildNode(ByVal tabCol As DataBaseTableColumn, ByVal parentNode As TreeNode)
        Dim aNode As New TreeNode()
        aNode.Tag = tabCol
        aNode.Text = Convert.ToString(tabCol.ColumnName) & " (" & Convert.ToString(tabCol.ColumnTypeName) & "(" & Convert.ToString(tabCol.MaxLength) & "), " & Convert.ToString(tabCol.IsNullableDisplay) & ")"
        parentNode.Nodes.Add(aNode)
    End Sub




    Private Sub TreeViewTable_AfterCheck(sender As System.Object, e As System.Windows.Forms.TreeViewEventArgs) Handles TreeViewTable.AfterCheck
        Dim aColumn As DataBaseTable = TryCast(e.Node.Tag, DataBaseTable)

        If e.Node.Checked AndAlso aColumn IsNot Nothing Then
            For Each aNode As TreeNode In e.Node.Nodes
                aNode.Checked = True
            Next
        Else
            For Each aNode As TreeNode In e.Node.Nodes
                aNode.Checked = False
            Next
        End If
    End Sub

    Private Sub btnCreate_Click(sender As System.Object, e As System.EventArgs) Handles btnCreate.Click
        Me.Cursor = Cursors.WaitCursor
        Try
            If Not String.IsNullOrEmpty(txtNamespace.Text) Then

                'Parcourir le 1er niveau du treeview (tables sql)
                For Each aNode As TreeNode In TreeViewTable.Nodes
                    If aNode.Checked Then

                        Dim aDBTable As DataBaseTable
                        aDBTable = New DataBaseTable With {.TableName = aNode.Text, .ClassNamespace = txtNamespace.Text, .TableColumnsList = New List(Of DataBaseTableColumn)}

                        'Parcourir le 2e niveau du treeview (colonnes sql)
                        For Each aChildNode As TreeNode In aNode.Nodes
                            If aChildNode.Checked Then
                                Dim aChildColumn As DataBaseTableColumn = TryCast(aChildNode.Tag, DataBaseTableColumn)
                                aDBTable.TableColumnsList.Add(aChildColumn)
                            End If
                        Next

                        If aDBTable.TableColumnsList.Count > 0 Then


                            xmlFilePath = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "CodeGen_" + aDBTable.TableName + ".xml")

                            'Transform l'objet DataBaseTable en xml
                            Serialize(aDBTable)

                            'Creation de la classe de proprietes 
                            If chkObj.Checked And Not String.IsNullOrEmpty(txtOuputPathObject.Text) Then
                                resultFilePath = txtOuputPathObject.Text + aDBTable.TableName + ".vb"
                                CreateObjectClass()
                            End If

                            'Creation de la classe Process Layer
                            If chkPL.Checked And Not String.IsNullOrEmpty(txtOuputPathPL.Text) Then
                                resultFilePath = txtOuputPathPL.Text + aDBTable.TableName + "_PL.vb"
                                CreatePL()
                            End If


                        End If
                    End If
                Next
            End If

            MessageWindow.ShowMessage("Files successfully created.", MsgBoxStyle.Information)
        Catch ex As Exception
            MessageWindow.ShowMessage(ex, MsgBoxStyle.Exclamation)
        Finally
            Me.Cursor = Cursors.Default
        End Try
    End Sub



    Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click
        btnNext.Enabled = False
        btnPrev.Enabled = True
        btnCreate.Enabled = True

        TabCtrl.SelectedTabPageIndex = 1
        TabCtrl.TabPages(1).PageEnabled = True
        TabCtrl.TabPages(0).PageEnabled = False

    End Sub

    Private Sub btnPrev_Click(sender As System.Object, e As System.EventArgs) Handles btnPrev.Click
        btnPrev.Enabled = False
        btnNext.Enabled = True
        btnCreate.Enabled = False

        TabCtrl.SelectedTabPageIndex = 0
        TabCtrl.TabPages(0).PageEnabled = True
        TabCtrl.TabPages(1).PageEnabled = False
    End Sub


    Private Sub btnPLDialog_Click(sender As System.Object, e As System.EventArgs) Handles btnPLDialog.Click
        If FolderBrowserPL.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            txtOuputPathPL.Text = FolderBrowserPL.SelectedPath + "\"
        End If
    End Sub

    Private Sub btnObjectDialog_Click(sender As System.Object, e As System.EventArgs) Handles btnObjectDialog.Click
        If FolderBrowserObject.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            txtOuputPathObject.Text = FolderBrowserObject.SelectedPath + "\"
        End If
    End Sub



    

    Private Sub Serialize(ByVal aTable As DataBaseTable)
        'Serialize object to a text file.
        aSerializer = New System.Xml.Serialization.XmlSerializer(aTable.GetType())

        Using objStreamWriter As New IO.StreamWriter(xmlFilePath)
            aSerializer.Serialize(objStreamWriter, aTable)
            objStreamWriter.Close()
        End Using
    End Sub

    Private Sub CreateObjectClass()
        Try
            With New System.Xml.Xsl.XslCompiledTransform
                .Load(txtTemplatePathObj.Text)
                .Transform(xmlFilePath, resultFilePath)
            End With
        Catch ex As Exception
            MessageBox.Show("Failed Layer : " & ex.Message)
        End Try
    End Sub

    Private Sub CreatePL()
        Try
            With New System.Xml.Xsl.XslCompiledTransform
                .Load(txtTemplatePathPL.Text)
                .Transform(xmlFilePath, resultFilePath)
            End With
        Catch ex As Exception
            MessageBox.Show("Failed Layer : " & ex.Message)
        End Try
    End Sub


End Class














